package lumis.portal.serviceinterfaceinstance;

import lumis.portal.PortalException;
import lumis.portal.PortalObjectAlreadyExistsException;
import lumis.portal.PortalObjectNotFoundException;
import lumis.portal.UnexpectedException;
import lumis.portal.dao.DaoException;
import lumis.portal.dao.jdbc.ITransactionJdbc;
import lumis.util.ITransaction;
import lumis.util.JdbcUtil;
import lumis.util.PortalUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Collection;
import java.util.HashMap;

/**
 * JDBC based DAO implementation for service interface instance.
 *
 * @since 4.0.0
 * @version $Revision: 13166 $ $Date: 2011-06-22 19:32:35 -0300 (Wed, 22 Jun 2011) $
 */
public class ServiceInterfaceInstanceDaoJdbc implements IServiceInterfaceInstanceDao
{
	public ServiceInterfaceInstanceConfig get(String interfaceInstanceId, ITransaction transaction) throws DaoException, PortalException
	{
		try
		{
			ServiceInterfaceInstanceConfig serviceInterfaceInstanceConfig = null;

			String strQuery = "SELECT interfaceid, serviceInstanceId, pageId, isCreatedRunTime, title, interfaceStyleId, width, height, paddingLeft, paddingRight, paddingTop, paddingBottom, colspan, rowspan, isPrintable, holderPriority, parentInterfaceId, renderAs, showTitleBar, enableCache, cacheLevel, cachePersonalization, cacheServerSideInclude, displayName, owner, disableInContextEdit FROM lum_InterfaceInstance WHERE interfaceInstanceId=?";

			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();
			PreparedStatement statement = connection.prepareStatement(strQuery);

			statement.setString(1, interfaceInstanceId);
			ResultSet resultSet = statement.executeQuery();

			String stringValue = null;

			if (resultSet.next())
			{
				serviceInterfaceInstanceConfig = new ServiceInterfaceInstanceConfig();

				serviceInterfaceInstanceConfig.setId(interfaceInstanceId);
				serviceInterfaceInstanceConfig.setServiceInterfaceId(resultSet.getString("interfaceid"));
				serviceInterfaceInstanceConfig.setServiceInstanceId(resultSet.getString("serviceInstanceId"));
				serviceInterfaceInstanceConfig.setOwner(resultSet.getString("owner"));
				String pageId = resultSet.getString("pageId");
				if(pageId != null)
				{
					serviceInterfaceInstanceConfig.setPageId(pageId);
				}
				serviceInterfaceInstanceConfig.setCreatedRunTime(resultSet.getInt("isCreatedRunTime") == 1 ? true : false);
				serviceInterfaceInstanceConfig.setTitle(resultSet.getString("title"));
				serviceInterfaceInstanceConfig.setServiceInterfaceStyleId(resultSet.getString("interfaceStyleId"));
				serviceInterfaceInstanceConfig.setWidth(resultSet.getString("width"));
				serviceInterfaceInstanceConfig.setHeight(resultSet.getString("height"));
				serviceInterfaceInstanceConfig.setPaddingLeft(resultSet.getString("paddingLeft"));
				serviceInterfaceInstanceConfig.setPaddingRight(resultSet.getString("paddingRight"));
				serviceInterfaceInstanceConfig.setPaddingTop(resultSet.getString("paddingTop"));
				serviceInterfaceInstanceConfig.setPaddingBottom(resultSet.getString("paddingBottom"));
				serviceInterfaceInstanceConfig.setRenderAs(resultSet.getInt("renderAs"));
				serviceInterfaceInstanceConfig.setShowTitleBar(resultSet.getInt("showTitleBar") > 0);
				serviceInterfaceInstanceConfig.setEnableCache(resultSet.getInt("enableCache"));
				serviceInterfaceInstanceConfig.setCacheLevel(resultSet.getInt("cacheLevel"));
				serviceInterfaceInstanceConfig.setCachePersonalization(resultSet.getInt("cachePersonalization"));
				serviceInterfaceInstanceConfig.setCacheServerSideInclude(resultSet.getInt("cacheServerSideInclude"));
				serviceInterfaceInstanceConfig.setDisableInContextEdit(resultSet.getInt("disableInContextEdit") > 0);
				serviceInterfaceInstanceConfig.setDisplayName(resultSet.getString("displayName"));

				stringValue = resultSet.getString("colspan");
				if (stringValue != null)
					serviceInterfaceInstanceConfig.setColspan(new Integer(stringValue));

				stringValue = resultSet.getString("rowspan");
				if (stringValue != null)
					serviceInterfaceInstanceConfig.setRowspan(new Integer(stringValue));

				serviceInterfaceInstanceConfig.setPrintable(resultSet.getInt("isPrintable"));

				stringValue = resultSet.getString("holderPriority");
				if (stringValue != null)
					serviceInterfaceInstanceConfig.setHolderPriority(new Integer(stringValue));

				stringValue = resultSet.getString("parentInterfaceId");
				if (stringValue != null)
					serviceInterfaceInstanceConfig.setParentInterfaceInstanceId(stringValue);

			}

			resultSet.close();
			statement.close();

			if (serviceInterfaceInstanceConfig == null)
				throw new PortalObjectNotFoundException("STR_INTERFACE_DOES_NOT_EXIST;" + interfaceInstanceId);

			return serviceInterfaceInstanceConfig;
		}
		catch (PortalException e)
		{
			throw e;
		}
		catch (Exception e)
		{
			throw new UnexpectedException(e);
		}

	}

	public void add(ServiceInterfaceInstanceConfig serviceInterfaceInstance, ITransaction transaction) throws DaoException, PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection
					.prepareStatement("insert into lum_InterfaceInstance (interfaceInstanceId, interfaceId, serviceInstanceId, pageId, isCreatedRunTime, title, interfaceStyleId, width, height, paddingLeft, paddingRight, paddingTop, paddingBottom, colspan, rowspan, isPrintable, holderPriority, parentInterfaceId, renderAs, showTitleBar, enableCache, cacheLevel, cachePersonalization, cacheServerSideInclude, displayName, owner, disableInContextEdit) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

			statement.setString(1, serviceInterfaceInstance.getId());
			statement.setString(2, serviceInterfaceInstance.getServiceInterfaceId());
			statement.setString(3, serviceInterfaceInstance.getServiceInstanceId());
			statement.setString(4, serviceInterfaceInstance.getPageId());
			statement.setInt(5, serviceInterfaceInstance.isCreatedRunTime() ? 1 : 0);
			statement.setString(6, serviceInterfaceInstance.getTitle());
			statement.setString(7, serviceInterfaceInstance.getServiceInterfaceStyleId());
			statement.setString(8, serviceInterfaceInstance.getWidth());
			statement.setString(9, serviceInterfaceInstance.getHeight());
			statement.setString(10, serviceInterfaceInstance.getPaddingLeft());
			statement.setString(11, serviceInterfaceInstance.getPaddingRight());
			statement.setString(12, serviceInterfaceInstance.getPaddingTop());
			statement.setString(13, serviceInterfaceInstance.getPaddingBottom());

			if (serviceInterfaceInstance.getColspan() != null)
				statement.setInt(14, serviceInterfaceInstance.getColspan().intValue());
			else
				statement.setString(14, null);

			if (serviceInterfaceInstance.getRowspan() != null)
				statement.setInt(15, serviceInterfaceInstance.getRowspan().intValue());
			else
				statement.setString(15, null);

			statement.setInt(16, serviceInterfaceInstance.getPrintable());

			if (serviceInterfaceInstance.getHolderPriority() != null)
				statement.setInt(17, serviceInterfaceInstance.getHolderPriority().intValue());
			else
				statement.setString(17, null);

			if (serviceInterfaceInstance.getParentInterfaceInstanceId() != null)
				statement.setString(18, serviceInterfaceInstance.getParentInterfaceInstanceId());
			else
				statement.setString(18, null);

			statement.setInt(19, serviceInterfaceInstance.getRenderAs());
			statement.setInt(20, serviceInterfaceInstance.isShowTitleBar() ? 1 : 0);
			statement.setInt(21, serviceInterfaceInstance.getEnableCache());
			statement.setInt(22, serviceInterfaceInstance.getCacheLevel());
			statement.setInt(23, serviceInterfaceInstance.getCachePersonalization());
			statement.setInt(24, serviceInterfaceInstance.getCacheServerSideInclude());
			statement.setString(25, serviceInterfaceInstance.getDisplayName());

			// do not store default owner
			IServiceInterfaceInstanceOwner owner = serviceInterfaceInstance.getOwner();
			if (owner instanceof PageInterfaceInstanceOwner)
				statement.setString(26, null);
			else
				statement.setString(26, owner.getClass().getName());

			statement.setInt(27, serviceInterfaceInstance.isInContextEditDisabled() ? 1 : 0);
			try
			{
				statement.executeUpdate();
			}
			catch (Exception e)
			{
				try
				{
					if (get(serviceInterfaceInstance.getId(), daoTransactionJdbc) != null)
						throw new PortalObjectAlreadyExistsException("STR_RESOURCE_ALREADY_EXISTS;" + serviceInterfaceInstance.getId(), e);
					else
						throw e;
				}
				catch (PortalObjectNotFoundException e1)
				{
					throw e;
				}
			}
			statement.close();
		}
		catch (PortalException e)
		{
			throw e;
		}
		catch (Exception e)
		{
			throw new DaoException("STR_ERROR_ADDING_INTERFACE_INSTANCE_FOR_SERVICE_INSTANCE;"+serviceInterfaceInstance.getServiceInstanceId(), e);
		}
	}

	public void update(ServiceInterfaceInstanceConfig serviceInterfaceInstance, ITransaction transaction) throws DaoException, PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection
					.prepareStatement("update lum_InterfaceInstance set interfaceId=?, serviceInstanceId=?, pageId=?, isCreatedRunTime=?, title=?, interfaceStyleId=?, width=?, height=?, paddingLeft=?, paddingRight=?, paddingTop=?, paddingBottom=?, colspan=?, rowspan=?, isPrintable=?, holderPriority=?, parentInterfaceId=?, renderAs=?, showTitleBar=?, enableCache=?, cacheLevel=?, cachePersonalization=?, cacheServerSideInclude=?, displayName=?, owner=?, disableInContextEdit=? where interfaceInstanceId=?");

			statement.setString(1, serviceInterfaceInstance.getServiceInterfaceId());
			statement.setString(2, serviceInterfaceInstance.getServiceInstanceId());
			statement.setString(3, serviceInterfaceInstance.getPageId());
			statement.setInt(4, serviceInterfaceInstance.isCreatedRunTime() ? 1 : 0);
			statement.setString(5, serviceInterfaceInstance.getTitle());
			statement.setString(6, serviceInterfaceInstance.getServiceInterfaceStyleId());
			statement.setString(7, serviceInterfaceInstance.getWidth());
			statement.setString(8, serviceInterfaceInstance.getHeight());
			statement.setString(9, serviceInterfaceInstance.getPaddingLeft());
			statement.setString(10, serviceInterfaceInstance.getPaddingRight());
			statement.setString(11, serviceInterfaceInstance.getPaddingTop());
			statement.setString(12, serviceInterfaceInstance.getPaddingBottom());
			if (serviceInterfaceInstance.getColspan() != null)
				statement.setInt(13, serviceInterfaceInstance.getColspan().intValue());
			else
				statement.setString(13, null);

			if (serviceInterfaceInstance.getRowspan() != null)
				statement.setInt(14, serviceInterfaceInstance.getRowspan().intValue());
			else
				statement.setString(14, null);

			statement.setInt(15, serviceInterfaceInstance.getPrintable());

			if (serviceInterfaceInstance.getHolderPriority() != null)
				statement.setInt(16, serviceInterfaceInstance.getHolderPriority().intValue());
			else
				statement.setString(16, null);

			if (serviceInterfaceInstance.getParentInterfaceInstanceId() != null)
				statement.setString(17, serviceInterfaceInstance.getParentInterfaceInstanceId());
			else
				statement.setString(17, null);

			statement.setInt(18, serviceInterfaceInstance.getRenderAs());
			statement.setInt(19, serviceInterfaceInstance.isShowTitleBar() ? 1 : 0);
			statement.setInt(20, serviceInterfaceInstance.getEnableCache());
			statement.setInt(21, serviceInterfaceInstance.getCacheLevel());
			statement.setInt(22, serviceInterfaceInstance.getCachePersonalization());
			statement.setInt(23, serviceInterfaceInstance.getCacheServerSideInclude());
			statement.setString(24, serviceInterfaceInstance.getDisplayName());

			// do not store default owner
			IServiceInterfaceInstanceOwner owner = serviceInterfaceInstance.getOwner();
			if (owner instanceof PageInterfaceInstanceOwner)
				statement.setString(25, null);
			else
				statement.setString(25, owner.getClass().getName());

			statement.setInt(26, serviceInterfaceInstance.isInContextEditDisabled() ? 1 : 0);
			statement.setString(27, serviceInterfaceInstance.getId());

			statement.execute();
			statement.close();
		}
		catch (PortalException e)
		{
			throw e;
		}
		catch (Exception e)
		{
			throw new UnexpectedException(e);
		}
	}

	public void delete(String serviceInterfaceInstanceId, ITransaction transaction) throws DaoException, PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection.prepareStatement("delete from lum_InterfaceInstance where interfaceInstanceId = ?");

			statement.setString(1, serviceInterfaceInstanceId);
			statement.executeUpdate();
			statement.close();
		}
		/*
		 * catch (DaoException e) { throw e; }
		 */
		catch (Exception e)
		{
			throw new UnexpectedException(e);
		}
	}

	public ServiceInterfaceInstanceConfig getRunTimeInstance(String serviceInterfaceId, String serviceInstanceId, String pageId, ITransaction transaction) throws DaoException, PortalException
	{
		final String queryRuntimeInstanceNoServiceInstanceId = "SELECT interfaceInstanceId, interfaceid, serviceInstanceId, pageId, isCreatedRunTime, title, width, height, paddingLeft, paddingRight, paddingTop, paddingBottom, colspan, rowspan, isPrintable, holderPriority, renderAs, showTitleBar, enableCache, cacheLevel, cachePersonalization, cacheServerSideInclude, displayName, owner, disableInContextEdit FROM lum_InterfaceInstance WHERE isCreatedRunTime=1 and interfaceId=? and serviceInstanceId is null and pageId=?";
		final String queryRuntimeInstanceServiceInstanceId = "SELECT interfaceInstanceId, interfaceid, serviceInstanceId, pageId, isCreatedRunTime, title, width, height, paddingLeft, paddingRight, paddingTop, paddingBottom, colspan, rowspan, isPrintable, holderPriority, renderAs, showTitleBar, enableCache, cacheLevel, cachePersonalization, cacheServerSideInclude, displayName, owner, disableInContextEdit FROM lum_InterfaceInstance WHERE isCreatedRunTime=1 and interfaceId=? and serviceInstanceId=? and pageId=?";
		try
		{
			ServiceInterfaceInstanceConfig serviceInterfaceInstanceConfig = null;
			String strQuery = serviceInstanceId != null ? queryRuntimeInstanceServiceInstanceId : queryRuntimeInstanceNoServiceInstanceId;

			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();
			PreparedStatement statement = connection.prepareStatement(strQuery);

			statement.setString(1, serviceInterfaceId);
			if (serviceInstanceId != null)
				statement.setString(2, serviceInstanceId);
			statement.setString(serviceInstanceId != null ? 3 : 2, pageId);
			ResultSet resultSet = statement.executeQuery();

			if (resultSet.next())
			{
				serviceInterfaceInstanceConfig = new ServiceInterfaceInstanceConfig();

				serviceInterfaceInstanceConfig.setId(resultSet.getString("interfaceInstanceId"));
				serviceInterfaceInstanceConfig.setServiceInterfaceId(resultSet.getString("interfaceid"));
				serviceInterfaceInstanceConfig.setServiceInstanceId(resultSet.getString("serviceInstanceId"));
				serviceInterfaceInstanceConfig.setOwner(resultSet.getString("owner"));
				String databasePageId = resultSet.getString("pageId");
				if(databasePageId != null)
				{
					serviceInterfaceInstanceConfig.setPageId(databasePageId);
				}
				serviceInterfaceInstanceConfig.setCreatedRunTime(resultSet.getInt("isCreatedRunTime") == 1 ? true : false);

				serviceInterfaceInstanceConfig.setTitle(resultSet.getString("title"));
				serviceInterfaceInstanceConfig.setWidth(resultSet.getString("width"));
				serviceInterfaceInstanceConfig.setHeight(resultSet.getString("height"));
				serviceInterfaceInstanceConfig.setPaddingLeft(resultSet.getString("paddingLeft"));
				serviceInterfaceInstanceConfig.setPaddingRight(resultSet.getString("paddingRight"));
				serviceInterfaceInstanceConfig.setPaddingTop(resultSet.getString("paddingTop"));
				serviceInterfaceInstanceConfig.setPaddingBottom(resultSet.getString("paddingBottom"));
				serviceInterfaceInstanceConfig.setColspan(new Integer(resultSet.getInt("colspan")));
				serviceInterfaceInstanceConfig.setRowspan(new Integer(resultSet.getInt("rowspan")));
				serviceInterfaceInstanceConfig.setPrintable(resultSet.getInt("isPrintable"));
				serviceInterfaceInstanceConfig.setHolderPriority(new Integer(resultSet.getInt("holderPriority")));
				serviceInterfaceInstanceConfig.setRenderAs(resultSet.getInt("renderAs"));
				serviceInterfaceInstanceConfig.setShowTitleBar(resultSet.getInt("showTitleBar") > 0);
				serviceInterfaceInstanceConfig.setEnableCache(resultSet.getInt("enableCache"));
				serviceInterfaceInstanceConfig.setCacheLevel(resultSet.getInt("cacheLevel"));
				serviceInterfaceInstanceConfig.setCachePersonalization(resultSet.getInt("cachePersonalization"));
				serviceInterfaceInstanceConfig.setCacheServerSideInclude(resultSet.getInt("cacheServerSideInclude"));
				serviceInterfaceInstanceConfig.setDisplayName(resultSet.getString("displayName"));
				serviceInterfaceInstanceConfig.setDisableInContextEdit(resultSet.getInt("disableInContextEdit") > 0);
			}

			resultSet.close();
			statement.close();

			if (serviceInterfaceInstanceConfig == null)
				throw new PortalObjectNotFoundException("STR_INTERFACE_DOES_NOT_EXIST");

			return serviceInterfaceInstanceConfig;
		}
		catch (PortalException e)
		{
			throw e;
		}
		catch (Exception e)
		{
			throw new UnexpectedException(e);
		}
	}

	public Collection<String> getPageInterfaceInstanceIds(String pageId, ITransaction transaction) throws DaoException, PortalException
	{
		try
		{
			String strQuery = "SELECT interfaceInstanceId FROM lum_InterfaceInstance WHERE pageId=?";

			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();
			PreparedStatement statement = connection.prepareStatement(strQuery);
			try
			{
				statement.setString(1, pageId);
				Collection<String> returnValues = JdbcUtil.fillListString(statement, "interfaceInstanceId");
				return returnValues;
			}
			finally
			{
				statement.close();
			}
		}
		catch (PortalException e)
		{
			throw e;
		}
		catch (Exception e)
		{
			throw new UnexpectedException(e);
		}
	}

	public Collection<String> getIdsByServiceInterfaceId(String serviceInterfaceId, boolean ignoreRuntimeInterfaces, ITransaction transaction) throws DaoException, PortalException
	{
		return getIdsByServiceInterfaceId(serviceInterfaceId, ignoreRuntimeInterfaces, false, transaction);
	}

	public Collection<String> getIdsByServiceInterfaceId(String serviceInterfaceId, boolean ignoreRuntimeInterfaces, boolean ignorePageTemplates, ITransaction transaction) throws DaoException, PortalException
	{
		try
		{
			String strQuery = "SELECT interfaceInstanceId FROM lum_InterfaceInstance WHERE interfaceId=?";

			if(ignoreRuntimeInterfaces)
				strQuery += " AND isCreatedRuntime = 0";

			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();
			PreparedStatement statement = connection.prepareStatement(strQuery);
			try
			{
				statement.setString(1, serviceInterfaceId);
				Collection<String> returnValues = JdbcUtil.fillListString(statement, "interfaceInstanceId");
				return returnValues;

			}
			finally
			{
				statement.close();
			}
		}
		catch (PortalException e)
		{
			throw e;
		}
		catch (Exception e)
		{
			throw new UnexpectedException(e);
		}
	}

	public Collection<String> getIdsByServiceInterfaceIdAndServiceInstanceId(String serviceInterfaceId, String serviceInstanceId, boolean ignoreRuntimeInterfaces, ITransaction transaction) throws DaoException, PortalException
	{
		return getIdsByServiceInterfaceIdAndServiceInstanceId(serviceInterfaceId, serviceInstanceId, ignoreRuntimeInterfaces, false, transaction);
	}

	public Collection<String> getIdsByServiceInterfaceIdAndServiceInstanceId(String serviceInterfaceId, String serviceInstanceId, boolean ignoreRuntimeInterfaces, boolean ignorePageTemplates, ITransaction transaction) throws DaoException, PortalException
	{
		try
		{
			String strQuery = "SELECT ii.interfaceInstanceId FROM lum_InterfaceInstance ii" + (ignorePageTemplates ? " INNER JOIN lum_Page p ON ii.pageId = p.pageId" : "") + " WHERE ii.interfaceId=? AND ii.serviceInstanceId=?" + (ignorePageTemplates ? " AND p.isTemplate = 0 " : "");

			if(ignoreRuntimeInterfaces)
				strQuery += " AND isCreatedRuntime = 0";

			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();
			PreparedStatement statement = connection.prepareStatement(strQuery);
			try
			{
				statement.setString(1, serviceInterfaceId);
				statement.setString(2, serviceInstanceId);

				Collection<String> returnValues = JdbcUtil.fillListString(statement, "interfaceInstanceId");
				return returnValues;
			}
			finally
			{
				statement.close();
			}
		}
		catch (PortalException e)
		{
			throw e;
		}
		catch (Exception e)
		{
			throw new UnexpectedException(e);
		}
	}

	public Collection<String> getIdsByServiceInstanceId(String serviceInstanceId, boolean includeRunTimeInterfaces, ITransaction transaction) throws DaoException, PortalException
	{
		try
		{
			String strQuery = "SELECT interfaceInstanceId FROM lum_InterfaceInstance WHERE serviceInstanceId=?";

			if(!includeRunTimeInterfaces)
				strQuery += " and isCreatedRunTime = 0";

			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();
			PreparedStatement statement = connection.prepareStatement(strQuery);
			try
			{
				statement.setString(1, serviceInstanceId);

				Collection<String> returnValues = JdbcUtil.fillListString(statement, "interfaceInstanceId");
				return returnValues;
			}
			finally
			{
				statement.close();
			}
		}
		catch (PortalException e)
		{
			throw e;
		}
		catch (Exception e)
		{
			throw new UnexpectedException(e);
		}
	}
	public Collection<String> getIdsByServiceInterfaceStyleId(String serviceInterfaceStyleId, String serviceInterfaceId, ITransaction transaction) throws DaoException, PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			String strQuery = "SELECT interfaceInstanceId FROM lum_InterfaceInstance WHERE interfaceId=? and interfaceStyleId " + (serviceInterfaceStyleId != null ? "=?" : "IS NULL");
			PreparedStatement statement = connection.prepareStatement(strQuery);
			try
			{
				statement.setString(1, serviceInterfaceId);
				if (serviceInterfaceStyleId != null)
					statement.setString(2, serviceInterfaceStyleId);

				Collection<String> serviceInterfaceInstanceIds = JdbcUtil.fillListString(statement, "interfaceInstanceId");
				return serviceInterfaceInstanceIds;
			}
			finally
			{
				statement.close();
			}
		}
		catch (PortalException e)
		{
			throw e;
		}
		catch (Exception e)
		{
			throw new UnexpectedException(e);
		}

	}

	public String getCustomProperty(String interfaceInstanceId, String name, ITransaction transaction) throws DaoException, PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			String strQuery = "select value from lum_IntfInstCustomProperty where interfaceInstanceId=? and name=?";
			PreparedStatement statement = connection.prepareStatement(strQuery);
			statement.setString(1, interfaceInstanceId);
			statement.setString(2, name);
			ResultSet resultSet = statement.executeQuery();

			String value = null;
			if (resultSet.next())
			{
				// the string returned by the db may occupy more space than required.
				// This value being cached in the Portal Cache and therefore reduce the
				// memory it occupies creating a new string.: PLPJ-6379
				String resultSetValue = resultSet.getString("value");
				if( resultSetValue!=null )
					value = new String(resultSetValue);
			}

			resultSet.close();
			statement.close();

			return value;
		}
		catch (PortalException e)
		{
			throw e;
		}
		catch (Exception e)
		{
			throw new UnexpectedException(e);
		}
	}

	public HashMap<String, String> getCustomProperties(String interfaceInstanceId, ITransaction transaction) throws DaoException, PortalException
	{
		try
		{
			HashMap<String, String> interfaceInstanceProperties = new HashMap<String, String>();

			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			String strQuery = "select name, value from lum_IntfInstCustomProperty where interfaceInstanceId=?";
			PreparedStatement statement = connection.prepareStatement(strQuery);
			statement.setString(1, interfaceInstanceId);
			ResultSet resultSet = statement.executeQuery();

			while(resultSet.next())
			{
				interfaceInstanceProperties.put(resultSet.getString("name"), resultSet.getString("value"));
			}

			resultSet.close();
			statement.close();

			return interfaceInstanceProperties;
		}
		catch (PortalException e)
		{
			throw e;
		}
		catch (Exception e)
		{
			throw new UnexpectedException(e);
		}
	}

	public void setCustomProperty(String interfaceInstanceId, String name, String value, ITransaction transaction) throws DaoException, PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			String curValue = getCustomProperty(interfaceInstanceId, name, transaction);

			// if property value has not changed, no need to update it in database
			if((value == null && curValue == null) || (value != null && value.equals(curValue)))
				return;

			PreparedStatement statement = connection.prepareStatement("update lum_IntfInstCustomProperty set value=? where interfaceInstanceId=? and name=?");
			statement.setString(1, value);
			statement.setString(2, interfaceInstanceId);
			statement.setString(3, name);

			int rowsAffected = statement.executeUpdate();
			statement.close();

			if (rowsAffected <= 0)
			{
				statement = connection.prepareStatement("insert into lum_IntfInstCustomProperty (customPropertyId, interfaceInstanceId, name, value) values(?,?,?,?)");
				statement.setString(1, PortalUtil.generateNewGuid());
				statement.setString(2, interfaceInstanceId);
				statement.setString(3, name);
				statement.setString(4, value);

				statement.executeUpdate();
				statement.close();
			}
		}
		catch (PortalException e)
		{
			throw e;
		}
		catch (Exception e)
		{
			throw new UnexpectedException(e);
		}
	}

	public void deleteCustomProperties(String interfaceInstanceId, ITransaction transaction) throws DaoException, PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection.prepareStatement("delete from lum_IntfInstCustomProperty where interfaceInstanceId = ?");

			statement.setString(1, interfaceInstanceId);
			statement.executeUpdate();
			statement.close();
		}
		catch (PortalException e)
		{
			throw e;
		}
		catch (Exception e)
		{
			throw new UnexpectedException(e);
		}
	}

	public void deleteCustomProperty(String interfaceInstanceId, String name, ITransaction transaction) throws DaoException, PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection.prepareStatement("delete from lum_IntfInstCustomProperty where interfaceInstanceId = ? and name=?");

			statement.setString(1, interfaceInstanceId);
			statement.setString(2, name);
			statement.executeUpdate();
			statement.close();
		}
		catch (PortalException e)
		{
			throw e;
		}
		catch (Exception e)
		{
			throw new UnexpectedException(e);
		}
	}
}
